﻿using Kingdee.BOS.App.Data;
using Kingdee.BOS;
using Kingdee.BOS.Contracts.Report;
using Kingdee.BOS.Core.Report;
using Kingdee.BOS.Util;
using Kingdee.BOS.Core;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Kingdee.BOS.Orm.DataEntity;
using Kingdee.BOS.Core.DynamicForm.PlugIn.Args;

namespace DM
{
    [HotUpdate]
    [Description("物料库存表")]
    public class MaterialInventory : SysReportBaseService
    {
        /// 初始化事件：在此事件中，设置报表基本属性
        public override void Initialize()
        {
            base.Initialize();
            // 简单账表类型：普通、树形、分页
            this.ReportProperty.ReportType = ReportType.REPORTTYPE_NORMAL;
            this.ReportProperty.BillKeyFieldName = "FBM";
            // 报表名称
            this.ReportProperty.ReportName = new LocaleValue("物料库存表", base.Context.UserLocale.LCID);
            this.IsCreateTempTableByPlugin = true;
            this.ReportProperty.IsUIDesignerColumns = true;
            this.ReportProperty.IsGroupSummary = true;
            this.ReportProperty.SimpleAllCols = false;
            // 单据主键：两行FID相同，则为同一单的两条分录，单据编号可以不重复显示
            this.ReportProperty.PrimaryKeyFieldName = "FBM";
            this.ReportProperty.IsDefaultOnlyDspSumAndDetailData = true;
            // 报表主键字段名：默认为FIDENTITYID，可以修改
            this.ReportProperty.IdentityFieldName = "FIDENTITYID";
            // 设置精度控制
            //List list = new List();
            //// 数量
            //list.Add(new DecimalControlField
            //{
            //    ByDecimalControlFieldName = "FQty",
            //    DecimalControlFieldName = "FUnitPrecision"
            //});
            //// 单价
            //list.Add(new DecimalControlField
            //{
            //    ByDecimalControlFieldName = "FTAXPRICE",
            //    DecimalControlFieldName = "FPRICEDIGITS"
            //});
            //// 金额
            //list.Add(new DecimalControlField
            //{
            //    ByDecimalControlFieldName = "FALLAMOUNT",
            //    DecimalControlFieldName = "FAMOUNTDIGITS"
            //});
            //this.ReportProperty.DecimalControlFieldList = list;
        }

        public override string GetTableName()
        {
            var result = base.GetTableName();
            return result;
        }

        public override void BuilderReportSqlAndTempTable(IRptParams filter, string tableName)
        {
            base.BuilderReportSqlAndTempTable(filter, tableName);
            // 拼接过滤条件 ： filter
            // 略
            // 默认排序字段：需要从filter中取用户设置的排序字段
            string seqFld = string.Format(base.KSQL_SEQ, "FBM");

            DateTime FStartDate;
            DateTime FEndDate;
            string FMATERIALID_idsum = "";
            StringBuilder stringBuilder = new StringBuilder();
            StringBuilder stringBuilder1 = new StringBuilder();

            Kingdee.BOS.Orm.DataEntity.DynamicObject dyFilter = filter.FilterParameter.CustomFilter;
            StringBuilder SQLFilter = new StringBuilder();
            StringBuilder SQLFilter1 = new StringBuilder();
            //FStartDate = Convert.ToDateTime(dyFilter["FStartDate"]);//开始日期
            FEndDate = Convert.ToDateTime(dyFilter["FEndDate"]);//最后发货日期
           // 过滤 日期
            if (!FEndDate.ToString().Contains("0001"))
            {
                SQLFilter.Append("and  t.FDATE<='" + FEndDate + "'");
            }
            DynamicObjectCollection dyCollection1 = dyFilter["FMATERIALID"] as DynamicObjectCollection;
            if (dyCollection1.Count > 0)
            {
                foreach (var dobj in dyCollection1)
                {
                    //string FSuppiler_id = Convert.ToString(dobj["FSUPPLIERID_id"]);
                    DynamicObject d = dobj["FMATERIALID"] as DynamicObject;
                    string FMATERIALIDNumber = d["Number"].ToString();
                    FMATERIALID_idsum = FMATERIALID_idsum + "'" + FMATERIALIDNumber + "'" + ",";
                }
                SQLFilter1.Append("and t.fnumber IN (" + FMATERIALID_idsum.TrimEnd(',') + ")");
            }
            string FEndDate1 = Kingdee.BOS.Util.ObjectUtils.Object2String(filter.FilterParameter.CustomFilter["FEndDate"]);
            if (FEndDate1 != null && FEndDate1.Length > 0)
            {

                if (!FEndDate1.ToString().Contains("0001"))
                {
                    stringBuilder.AppendFormat(" and t.FDATE<='{0}'", FEndDate1);
                    FEndDate = Convert.ToDateTime(FEndDate1);
                }
            }
            //获取过滤方案的基础资料数据包
            var bdDataObj = filter.FilterParameter.CustomFilter["FMATERIALID1"] as Kingdee.BOS.Orm.DataEntity.DynamicObject;
            if (bdDataObj != null && bdDataObj.DynamicObjectType.Properties.ContainsKey("Number"))
            {
                string number = bdDataObj["Number"].ToString();
                stringBuilder1.AppendFormat(" and t.fnumber = '{0}'", number);
            }

            // 取数SQL
            string sql = string.Format(@"/*dialect*/
select DISTINCT t2.FNUMBER,t3.FNAME,t5.FDATAVALUE,t7.FNAME FDW,T8.FNAME FFL,T3.FSPECIFICATION,t2.F_ORA_GOODSTATUS INTO #MATERIAL from  T_BD_MATERIAL T2 
LEFT JOIN T_BD_MATERIAL_L T3 ON T2.FMATERIALID=T3.FMATERIALID
LEFT JOIN T_BAS_ASSISTANTDATAENTRY T4 ON T2.F_ORA_PP=t4.FENTRYID
LEFT JOIN T_BAS_ASSISTANTDATAENTRY_L T5 ON T4.FENTRYID=T5.FENTRYID
LEFT JOIN t_BD_MaterialBase T6 ON T2.FMATERIALID=t6.FMATERIALID
LEFT JOIN T_BD_UNIT_L T7 ON T6.FBASEUNITID=T7.FUNITID
LEFT JOIN T_BD_MATERIALGROUP_L T8 ON T2.FMATERIALGROUP=T8.FID
WHERE T2.FDOCUMENTSTATUS='C' AND T3.FLOCALEID=2052 AND T5.FLOCALEID=2052
AND T7.FLOCALEID=2052 AND T2.FCreateOrgId=T2.FUSEORGID

--------------------------------------------------------------------------------------------------------
SELECT FNUMBER,FNAME,sum(FREALQTY)FREALQTY INTO #A FROM (
--------------------------------------------初始库存---------------------------------------------------
select T2.FNUMBER,T3.FNAME,SUM(T1.FQTY)FREALQTY from T_STK_INVINIT t
left join T_STK_INVINITDETAIL t1 on t.FID=t1.FID
left join T_BD_MATERIAL t2 on t1.FMATERIALID=t2.FMATERIALID
left join T_BD_STOCK_L t3 on t.FSTOCKID=t3.FSTOCKID
where t.FDOCUMENTSTATUS='C' AND T2.FCreateOrgId=T2.FUSEORGID
GROUP BY T2.FNUMBER,T3.FNAME
union all
--------------------------------------------销售退货---------------------------------------------------
select T2.FNUMBER,T3.FNAME,sum(FREALQTY)FREALQTY from T_SAL_RETURNSTOCK t
left join T_SAL_RETURNSTOCKENTRY t1 on t.FID=t1.FID
left join T_BD_MATERIAL t2 on t1.FMATERIALID=t2.FMATERIALID
left join T_BD_STOCK_L t3 on t1.FSTOCKID=t3.FSTOCKID
left join T_SAL_RETURNSTOCKFIN t4 on t.FID=t4.FID
where t.FDOCUMENTSTATUS='C' and t4.FISGENFORIOS=0 AND T2.FCreateOrgId=T2.FUSEORGID
{2}{5}
group by T2.FNUMBER,T3.FNAME
UNION ALL
--------------------------------------------其他入库---------------------------------------------------
select T2.FNUMBER,T3.FNAME,sum(FQTY)FREALQTY from T_STK_MISCELLANEOUS t
left join T_STK_MISCELLANEOUSENTRY t1 on t.FID=t1.FID
left join T_BD_MATERIAL t2 on t1.FMATERIALID=t2.FMATERIALID
left join T_BD_STOCK_L t3 on t1.FSTOCKID=t3.FSTOCKID
where t.FDOCUMENTSTATUS='C' AND T2.FCreateOrgId=T2.FUSEORGID
{2}{5}
group by T2.FNUMBER,T3.FNAME
UNION ALL
--------------------------------------------采购入库---------------------------------------------------
select T2.FNUMBER,T3.FNAME,sum(FREALQTY)FREALQTY from t_STK_InStock t
left join T_STK_INSTOCKENTRY t1 on t.FID=t1.FID
left join T_BD_MATERIAL t2 on t1.FMATERIALID=t2.FMATERIALID
left join T_BD_STOCK_L t3 on t1.FSTOCKID=t3.FSTOCKID
left join t_STK_InStockFin t4 on t.FID=t4.FID
where t.FDOCUMENTSTATUS='C' and t4.FISGENFORIOS=0 AND T2.FCreateOrgId=T2.FUSEORGID
{2}{5}
group by T2.FNUMBER,T3.FNAME
UNION ALL
--------------------------------------------直接调拨（调入）---------------------------------------------------
select T2.FNUMBER,T3.FNAME,sum(FQTY)FREALQTY from T_STK_STKTRANSFERIN t
left join T_STK_STKTRANSFERINENTRY t1 on t.FID=t1.FID
left join T_BD_MATERIAL t2 on t1.FMATERIALID=t2.FMATERIALID
left join T_BD_STOCK_L t3 on t1.FDESTSTOCKID=t3.FSTOCKID
where t.FDOCUMENTSTATUS='C' and t.FISINTERLEGALPERSON=0 AND T2.FCreateOrgId=T2.FUSEORGID
{2}{5}
group by T2.FNUMBER,T3.FNAME
UNION ALL
--------------------------------------------委外退料---------------------------------------------------
select T2.FNUMBER,T3.FNAME,sum(FQTY)FREALQTY  from T_SUB_RETURNMTRL t
left join T_SUB_RETURNMTRLENTRY t1 on t.FID=t1.FID
left join T_BD_MATERIAL t2 on t1.FMATERIALID=t2.FMATERIALID
left join T_BD_STOCK_L t3 on t1.FSTOCKID=t3.FSTOCKID 
where t.FDOCUMENTSTATUS='C' AND T2.FCreateOrgId=T2.FUSEORGID
{2}{5}
group by T2.FNUMBER,T3.FNAME
UNION ALL
--------------------------------------------组装拆卸单（组装-成品入）---------------------------------------------------
select T2.FNUMBER,T3.FNAME,sum(FQTY)FREALQTY from T_STK_ASSEMBLY t
left join T_STK_ASSEMBLYPRODUCT t1 on t.FID=t1.FID
left join T_BD_MATERIAL t2 on t1.FMATERIALID=t2.FMATERIALID
left join T_BD_STOCK_L t3 on t1.FSTOCKID=t3.FSTOCKID
where t.FDOCUMENTSTATUS='C' and t.FAFFAIRTYPE='Assembly' AND T2.FCreateOrgId=T2.FUSEORGID
{2}{5}
group by T2.FNUMBER,T3.FNAME
UNION ALL
--------------------------------------------组装拆卸单（拆卸-子件入）---------------------------------------------------
select T2.FNUMBER,T3.FNAME,sum(t5.FQTY)FREALQTY from T_STK_ASSEMBLY t
left join T_STK_ASSEMBLYPRODUCT t1 on t.FID=t1.FID
left join T_STK_ASSEMBLYSUBITEM t5 on t1.FENTRYID=t5.FENTRYID
left join T_BD_MATERIAL t2 on t5.FMATERIALID=t2.FMATERIALID
left join T_BD_STOCK_L t3 on t5.FSTOCKID=t3.FSTOCKID
where t.FDOCUMENTSTATUS='C' and t.FAFFAIRTYPE='Dassembly' AND T2.FCreateOrgId=T2.FUSEORGID
{2}{5}
group by T2.FNUMBER,T3.FNAME
)T
group by  FNUMBER,FNAME

SELECT FNUMBER,FNAME,SUM(FREALQTY)FREALQTY INTO #A1 FROM (
--------------------------------------------销售出库---------------------------------------------------
select T2.FNUMBER,T3.FNAME,-sum(FREALQTY)FREALQTY from T_SAL_OUTSTOCK t
left join T_SAL_OUTSTOCKENTRY t1 on t.FID=t1.FID
left join T_BD_MATERIAL t2 on t1.FMATERIALID=t2.FMATERIALID
left join T_BD_STOCK_L t3 on t1.FSTOCKID=t3.FSTOCKID
left join T_SAL_OUTSTOCKFIN t4 on t.FID=t4.FID
where t.FDOCUMENTSTATUS='C' and t4.FISGENFORIOS=0 AND T2.FCreateOrgId=T2.FUSEORGID
{2}{5}
group by T2.FNUMBER,T3.FNAME
UNION ALL
--------------------------------------------采购退料---------------------------------------------------
select T2.FNUMBER,T3.FNAME,-sum(FRMREALQTY)FREALQTY from t_PUR_MRB t
left join T_PUR_MRBENTRY t1 on t.FID=t1.FID
left join T_BD_MATERIAL t2 on t1.FMATERIALID=t2.FMATERIALID
left join T_BD_STOCK_L t3 on t1.FSTOCKID=t3.FSTOCKID
left join T_PUR_MRBFIN t4 on t.FID=t4.FID
where t.FDOCUMENTSTATUS='C' and t4.FISGENFORIOS=0 AND T2.FCreateOrgId=T2.FUSEORGID
group by T2.FNUMBER,T3.FNAME
UNION ALL
--------------------------------------------其他出库---------------------------------------------------
select T2.FNUMBER,T3.FNAME,-sum(FQTY)FREALQTY from T_STK_MISDELIVERY t
left join T_STK_MISDELIVERYENTRY t1 on t.FID=t1.FID
left join T_BD_MATERIAL t2 on t1.FMATERIALID=t2.FMATERIALID
left join T_BD_STOCK_L t3 on t1.FSTOCKID=t3.FSTOCKID
where t.FDOCUMENTSTATUS='C'  AND T2.FCreateOrgId=T2.FUSEORGID
{2}{5}
group by T2.FNUMBER,T3.FNAME
UNION ALL
--------------------------------------------委外领料---------------------------------------------------
select T2.FNUMBER,T3.FNAME,-sum(FACTUALQTY)FREALQTY from T_SUB_PICKMTRL t
left join T_SUB_PICKMTRLDATA t1 on t.FID=t1.FID
left join T_BD_MATERIAL t2 on t1.FMATERIALID=t2.FMATERIALID
left join T_BD_STOCK_L t3 on t1.FSTOCKID=t3.FSTOCKID
where t.FDOCUMENTSTATUS='C' AND T2.FCreateOrgId=T2.FUSEORGID
{2}{5}
group by T2.FNUMBER,T3.FNAME
UNION ALL
--------------------------------------------直接调拨（调出）---------------------------------------------------
select T2.FNUMBER,T3.FNAME,-sum(FQTY)FREALQTY from T_STK_STKTRANSFERIN t
left join T_STK_STKTRANSFERINENTRY t1 on t.FID=t1.FID
left join T_BD_MATERIAL t2 on t1.FMATERIALID=t2.FMATERIALID
left join T_BD_STOCK_L t3 on t1.FSRCSTOCKID=t3.FSTOCKID
where t.FDOCUMENTSTATUS='C' and t.FISINTERLEGALPERSON=0 AND T2.FCreateOrgId=T2.FUSEORGID
{2}{5}
group by T2.FNUMBER,T3.FNAME
UNION ALL
--------------------------------------------组装拆卸单（拆卸-成品出）---------------------------------------------------
select T2.FNUMBER,T3.FNAME,-sum(FQTY)FREALQTY from T_STK_ASSEMBLY t
left join T_STK_ASSEMBLYPRODUCT t1 on t.FID=t1.FID
left join T_BD_MATERIAL t2 on t1.FMATERIALID=t2.FMATERIALID
left join T_BD_STOCK_L t3 on t1.FSTOCKID=t3.FSTOCKID
where t.FDOCUMENTSTATUS='C' and t.FAFFAIRTYPE='Dassembly' AND T2.FCreateOrgId=T2.FUSEORGID
{2}{5}
group by T2.FNUMBER,T3.FNAME
UNION ALL
--------------------------------------------组装拆卸单（组装-子件出）---------------------------------------------------
select T2.FNUMBER,T3.FNAME,-sum(t5.FQTY)FREALQTY from T_STK_ASSEMBLY t
left join T_STK_ASSEMBLYPRODUCT t1 on t.FID=t1.FID
left join T_STK_ASSEMBLYSUBITEM t5 on t1.FENTRYID=t5.FENTRYID
left join T_BD_MATERIAL t2 on t5.FMATERIALID=t2.FMATERIALID
left join T_BD_STOCK_L t3 on t5.FSTOCKID=t3.FSTOCKID
where t.FDOCUMENTSTATUS='C' and t.FAFFAIRTYPE='Assembly' AND T2.FCreateOrgId=T2.FUSEORGID
{2}{5}
group by T2.FNUMBER,T3.FNAME
)T
GROUP BY FNUMBER,FNAME

SELECT FNUMBER,FNAME,SUM(FREALQTY)FREALQTY INTO #A2  FROM (
SELECT * FROM #A 
UNION ALL
SELECT * FROM #A1
)T
GROUP BY FNUMBER,FNAME

--------------------------------------------查询去年出库总量（只抓取“销售出库退货、委外领退补料”）---------------------------------------------------
SELECT FNUMBER,FNAME,ISNULL(SUM(FREALQTY),0)FREALQTY INTO #B FROM (
--------------------------------------------销售出库---------------------------------------------------
select T2.FNUMBER,T3.FNAME,sum(FREALQTY)FREALQTY from T_SAL_OUTSTOCK t
left join T_SAL_OUTSTOCKENTRY t1 on t.FID=t1.FID
left join T_BD_MATERIAL t2 on t1.FMATERIALID=t2.FMATERIALID
left join T_BD_STOCK_L t3 on t1.FSTOCKID=t3.FSTOCKID
left join T_SAL_OUTSTOCKFIN t4 on t.FID=t4.FID
where t.FDOCUMENTSTATUS='C' and t4.FISGENFORIOS=0 AND T2.FCreateOrgId=T2.FUSEORGID
AND T.FDATE>=DATEFROMPARTS(YEAR(DATEADD(YEAR, -1, '{4}')), 1, 1)
AND T.FDATE<=DATEFROMPARTS(YEAR(DATEADD(YEAR, -1, '{4}')), 12, 31)
{2}{5}
group by T2.FNUMBER,T3.FNAME
UNION ALL
--------------------------------------------委外领料---------------------------------------------------
select T2.FNUMBER,T3.FNAME,sum(FACTUALQTY)FREALQTY from T_SUB_PICKMTRL t
left join T_SUB_PICKMTRLDATA t1 on t.FID=t1.FID
left join T_BD_MATERIAL t2 on t1.FMATERIALID=t2.FMATERIALID
left join T_BD_STOCK_L t3 on t1.FSTOCKID=t3.FSTOCKID
where t.FDOCUMENTSTATUS='C' AND T2.FCreateOrgId=T2.FUSEORGID
AND T.FDATE>=DATEFROMPARTS(YEAR(DATEADD(YEAR, -1, '{4}')), 1, 1)
AND T.FDATE<=DATEFROMPARTS(YEAR(DATEADD(YEAR, -1, '{4}')), 12, 31)
{2}{5}
group by T2.FNUMBER,T3.FNAME
)T
GROUP BY FNUMBER,FNAME

SELECT FNUMBER,FNAME,SUM(FREALQTY)FREALQTY INTO #B1 FROM (
--------------------------------------------销售退货---------------------------------------------------
select T2.FNUMBER,T3.FNAME,sum(FREALQTY)FREALQTY from T_SAL_RETURNSTOCK t
left join T_SAL_RETURNSTOCKENTRY t1 on t.FID=t1.FID
left join T_BD_MATERIAL t2 on t1.FMATERIALID=t2.FMATERIALID
left join T_BD_STOCK_L t3 on t1.FSTOCKID=t3.FSTOCKID
left join T_SAL_RETURNSTOCKFIN t4 on t.FID=t4.FID
where t.FDOCUMENTSTATUS='C' and t4.FISGENFORIOS=0 AND T2.FCreateOrgId=T2.FUSEORGID
AND T.FDATE>=DATEFROMPARTS(YEAR(DATEADD(YEAR, -1, '{4}')), 1, 1)
AND T.FDATE<=DATEFROMPARTS(YEAR(DATEADD(YEAR, -1, '{4}')), 12, 31)
{2}{5}
group by T2.FNUMBER,T3.FNAME

UNION ALL
--------------------------------------------委外退料---------------------------------------------------
select T2.FNUMBER,T3.FNAME,sum(FQTY)FREALQTY  from T_SUB_RETURNMTRL t
left join T_SUB_RETURNMTRLENTRY t1 on t.FID=t1.FID
left join T_BD_MATERIAL t2 on t1.FMATERIALID=t2.FMATERIALID
left join T_BD_STOCK_L t3 on t1.FSTOCKID=t3.FSTOCKID
where t.FDOCUMENTSTATUS='C' AND T2.FCreateOrgId=T2.FUSEORGID
AND T.FDATE>=DATEFROMPARTS(YEAR(DATEADD(YEAR, -1, '{4}')), 1, 1)
AND T.FDATE<=DATEFROMPARTS(YEAR(DATEADD(YEAR, -1, '{4}')), 12, 31)
{2}{5}
group by T2.FNUMBER,T3.FNAME
)T
GROUP BY 
FNUMBER,FNAME

--------------------------------------------计算周转天数---------------------------------------------------
--------------------------------------------销售出库(前90天出库量)---------------------------------------------------
select T2.FNUMBER,T3.FNAME,sum(FREALQTY)FREALQTY90,(sum(FREALQTY)/90)FRJL INTO #C from T_SAL_OUTSTOCK t
left join T_SAL_OUTSTOCKENTRY t1 on t.FID=t1.FID
left join T_BD_MATERIAL t2 on t1.FMATERIALID=t2.FMATERIALID
left join T_BD_STOCK_L t3 on t1.FSTOCKID=t3.FSTOCKID
left join T_SAL_OUTSTOCKFIN t4 on t.FID=t4.FID
where t.FDOCUMENTSTATUS='C' and t4.FISGENFORIOS=0 AND T2.FCreateOrgId=T2.FUSEORGID
AND T.FDATE>=DATEADD(DAY, -90, '{4}') and T.FDATE<='{4}'
group by T2.FNUMBER,T3.FNAME

--------------------------------------------查询即时库存---------------------------------------------------
SELECT FNUMBER,FNAME,SUM(FBASEQTY)FBASEQTY,SUM(FZZTS)FZZTS INTO #C1 FROM (
select t1.FNUMBER,t2.FNAME,ISNULL(SUM(FBASEQTY),0)FBASEQTY,ISNULL((sum(FBASEQTY)/t3.FRJL),0)FZZTS from T_STK_INVENTORY t
left join T_BD_MATERIAL t1 on t.FMATERIALID=t1.FMATERIALID
left join T_BD_STOCK_L t2 on t.FSTOCKID=t2.FSTOCKID 
left join #C t3 on t1.FNUMBER=t3.FNUMBER and t2.FNAME=t3.FNAME
group by t1.FNUMBER,t2.FNAME,t3.FRJL,FBASEQTY
)T
GROUP BY FNUMBER,FNAME

--------------------------------------------查询采购价目表，优先获取委外价---------------------------------------------------
select * into #D from (
select t.FMATERIALID,
t.FTaxPrice,
t1.FNUMBER,T3.FNAME,T2.FPRICETYPE,
ROW_NUMBER() OVER (PARTITION BY t.FMATERIALID,t3.fname ORDER BY t2.FPRICETYPE DESC, t.FTaxPrice DESC) AS row_num
from t_PUR_PriceListEntry T
left join T_BD_MATERIAL t1 on t.FMATERIALID=t1.FMATERIALID
LEFT JOIN t_PUR_PriceList T2 ON T.FID=T2.FID
LEFT JOIN T_BD_SUPPLIER_L T3 ON T2.FSUPPLIERID=T3.FSUPPLIERID
where t.FEFFECTIVEDATE<='{4}' and t.FEXPIRYDATE>='{4}' AND T1.FCreateOrgId=T1.FUSEORGID
   )t
   where t.row_num=1
--------------------------------------------查询物料清单---------------------------------------------------
SELECT T.FNUMBER, T2.FNUMBER FXBM,SUM(((T1.FNUMERATOR/T1.FDENOMINATOR)*ISNULL(t4.FTAXPRICE,0)))FTAXPRICE
INTO #D1
FROM T_ENG_BOM t
left join T_ENG_BOMCHILD t1 on t.FID=t1.FID
left join T_BD_MATERIAL t2 on t.FMATERIALID=t2.FMATERIALID
left join T_BD_MATERIAL t3 on t1.FMATERIALID=t3.FMATERIALID
left join #D t4 on t3.FNUMBER=t4.FNUMBER
WHERE T.FDOCUMENTSTATUS='C' and t.FUSEORGID=T.FCREATEORGID AND t4.FTAXPRICE<>0 
GROUP BY T.FNUMBER,T2.FNUMBER
-----------------------------------------------------------------------------------------------
SELECT T.FNUMBER FBM,T2.FNAME FMC,T.FNAME FCK,T2.FDATAVALUE FPP,T2.FSPECIFICATION,T2.FDW,T2.FFL
,CASE WHEN T2.F_ORA_GOODSTATUS='1' THEN '正常'
WHEN T2.F_ORA_GOODSTATUS='2' THEN '淘汰' ELSE '' END FCPZT
,ISNULL(T.FREALQTY,0)FQTY,
ISNULL(T9.FREALQTY,0)-ISNULL(T10.FREALQTY,0) FCKQTY,
ISNULL(T11.FREALQTY90,0)FREALQTY90,ISNULL(T12.FZZTS,0)FZZTS,
CASE WHEN ISNULL(T14.FTAXPRICE,0)<>0  THEN ISNULL(T14.FTAXPRICE,0)+ISNULL(T13.FTAXPRICE,0) ELSE ISNULL(T13.FTAXPRICE,0) END FTAXPRICE,

CASE WHEN ISNULL(T14.FTAXPRICE,0)<>0   THEN ISNULL(T.FREALQTY,0)*(ISNULL(T14.FTAXPRICE,0)+ISNULL(T13.FTAXPRICE,0))
ELSE (ISNULL((T9.FREALQTY-T10.FREALQTY),0)*ISNULL(T13.FTAXPRICE,0)) END  FAMOUNT,

CASE WHEN ISNULL(T14.FTAXPRICE,0)<>0   THEN (ISNULL(T9.FREALQTY,0)-ISNULL(T10.FREALQTY,0))*(ISNULL(T14.FTAXPRICE,0)+ISNULL(T13.FTAXPRICE,0))
ELSE (ISNULL((T9.FREALQTY-T10.FREALQTY),0)*ISNULL(T13.FTAXPRICE,0)) END FOUTAMOUNT
into #E
FROM #A2 T
--LEFT JOIN #A1 T1 ON T.FNUMBER=T1.FNUMBER AND T.FNAME=T1.FNAME
--LEFT JOIN T_BD_MATERIAL T2 ON T.FNUMBER=T2.FNUMBER
--LEFT JOIN T_BD_MATERIAL_L T3 ON T2.FMATERIALID=T3.FMATERIALID
--LEFT JOIN T_BAS_ASSISTANTDATAENTRY T4 ON T2.F_ORA_PP=t4.FENTRYID
--LEFT JOIN T_BAS_ASSISTANTDATAENTRY_L T5 ON T4.FENTRYID=T5.FENTRYID
--LEFT JOIN t_BD_MaterialBase T6 ON T2.FMATERIALID=t6.FMATERIALID
--LEFT JOIN T_BD_UNIT_L T7 ON T6.FBASEUNITID=T7.FUNITID
--LEFT JOIN T_BD_MATERIALGROUP_L T8 ON T2.FMATERIALGROUP=T8.FID
LEFT JOIN #MATERIAL T2 ON T.FNUMBER=T2.FNUMBER  
LEFT JOIN #B T9 ON T.FNUMBER=T9.FNUMBER AND T.FNAME=T9.FNAME
LEFT JOIN #B1 T10 ON T.FNUMBER=T10.FNUMBER AND T.FNAME=T10.FNAME
LEFT JOIN #C T11 ON T.FNUMBER=T11.FNUMBER AND T.FNAME=T11.FNAME
LEFT JOIN #C1 T12 ON T.FNUMBER=T12.FNUMBER AND T.FNAME=T12.FNAME
LEFT JOIN #D1 T13 ON T.FNUMBER=T13.FXBM
LEFT JOIN #D T14 ON T.FNUMBER=T14.FNUMBER
WHERE T2.FNUMBER IS NOT NULL
{3}{6}
SELECT DISTINCT * into #E1 FROM #E
SELECT ROW_NUMBER() OVER (ORDER BY FBM) AS FXH,*,{0} INTO {1} FROM #E1 where (FQTY is not null and FQTY<>0)
                                        ", seqFld, tableName, SQLFilter, SQLFilter1, FEndDate, stringBuilder, stringBuilder1);
            DBUtils.ExecuteDynamicObject(this.Context, sql);
        }

        protected override string GetIdentityFieldIndexSQL(string tableName)
        {
            string result = base.GetIdentityFieldIndexSQL(tableName);
            return result;
        }
        public override ReportTitles GetReportTitles(IRptParams filter)
        {
            var rptTitles = new ReportTitles();
            if (filter == null || filter.FilterParameter == null || filter.FilterParameter.CustomFilter == null)
                return rptTitles;
            string FEndDate = Kingdee.BOS.Util.ObjectUtils.Object2String(filter.FilterParameter.CustomFilter["FEndDate"]);
            rptTitles.Add(new ReportTitle("FEndDate", FEndDate));

            //获取过滤方案的基础资料内码
            var bdPkVal = Kingdee.BOS.Util.ObjectUtils.Object2Int(filter.FilterParameter.CustomFilter["FMATERIALID1_Id"]);
            if (bdPkVal != 0)
            {
                rptTitles.Add(new ReportTitle("FMATERIALID1", bdPkVal.ToString()));
            }

            return rptTitles;
        }
        protected override void ExecuteBatch(List<string> listSql)
        {
            base.ExecuteBatch(listSql);
        }

        protected override string GetSummaryColumsSQL(List<SummaryField> summaryFields)
        {
            var result = base.GetSummaryColumsSQL(summaryFields);
            return result;
        }

        protected override System.Data.DataTable GetListData(string sSQL)
        {
            var result = base.GetListData(sSQL);
            return result;
        }

        protected override System.Data.DataTable GetReportData(IRptParams filter)
        {
            var result = base.GetReportData(filter);
            return result;
        }

        protected override System.Data.DataTable GetReportData(string tablename, IRptParams filter)
        {
            var result = base.GetReportData(tablename, filter);
            return result;
        }

        public override int GetRowsCount(IRptParams filter)
        {
            var result = base.GetRowsCount(filter);
            return result;
        }

        protected override string BuilderFromWhereSQL(IRptParams filter)
        {
            string result = base.BuilderFromWhereSQL(filter);
            return result;
        }

        protected override string BuilderSelectFieldSQL(IRptParams filter)
        {
            string result = base.BuilderSelectFieldSQL(filter);
            return result;
        }

        protected override string BuilderTempTableOrderBySQL(IRptParams filter)
        {
            string result = base.BuilderTempTableOrderBySQL(filter);
            return result;
        }

        public override void CloseReport()
        {
            base.CloseReport();
        }

        protected override string CreateGroupSummaryData(IRptParams filter, string tablename)
        {
            string result = base.CreateGroupSummaryData(filter, tablename);
            return result;
        }

        protected override void CreateTempTable(string sSQL)
        {
            base.CreateTempTable(sSQL);
        }

        public override void DropTempTable()
        {
            base.DropTempTable();
        }

        public override System.Data.DataTable GetList(IRptParams filter)
        {
            var result = base.GetList(filter);
            return result;
        }

        public override List<long> GetOrgIdList(IRptParams filter)
        {
            var result = base.GetOrgIdList(filter);
            return result;
        }

        public override List<Kingdee.BOS.Core.Metadata.TreeNode> GetTreeNodes(IRptParams filter)
        {
            var result = base.GetTreeNodes(filter);
            return result;
        }
    }
}
